CREATE DATABASE CampusClubManagementSystem;

USE CampusClubManagementSystem;

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE,
    EnrollmentYear INT,
    Course VARCHAR(100)
);

CREATE TABLE Club (
    ClubID INT PRIMARY KEY,
    ClubName VARCHAR(100),
    Category VARCHAR(50),
    FoundingDate DATE
);

CREATE TABLE Membership (
    MembershipID INT PRIMARY KEY,
    StudentID INT,
    ClubID INT,
    JoinDate DATE,
    Role VARCHAR(50),
    Status VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE Event (
    EventID INT PRIMARY KEY,
    ClubID INT,
    EventName VARCHAR(100),
    EventDate DATE,
    Venue VARCHAR(100),
    AttendanceLimit INT,
    Description TEXT,
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE Participation (
    ParticipationID INT PRIMARY KEY,
    StudentID INT,
    EventID INT,
    AttendanceStatus VARCHAR(50),
    Feedback TEXT,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (EventID) REFERENCES Event(EventID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE Announcement (
    AnnouncementID INT PRIMARY KEY,
    ClubID INT,
    Title VARCHAR(100),
    Content TEXT,
    DateSent DATE,
    SenderName VARCHAR(100),
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

INSERT INTO Student (StudentID, FirstName, LastName, Email, EnrollmentYear, Course) VALUES
(1, 'Alice', 'Johnson', 'alice.johnson@university.edu', 2022, 'Computer Science'),
(2, 'Michael', 'Baker', 'michael.baker@university.edu', 2021, 'Business Management'),
(3, 'Sophia', 'Lee', 'sophia.lee@university.edu', 2023, 'Mechanical Engineering'),
(4, 'Liam', 'Anderson', 'liam.anderson@university.edu', 2020, 'Mathematics'),
(5, 'Emma', 'Davis', 'emma.davis@university.edu', 2022, 'Architecture');


INSERT INTO Club (ClubID, ClubName, Category, FoundingDate) VALUES
(1, 'Tech Innovators', 'Academic', '2018-09-01'),
(2, 'Green Planet', 'Cultural', '2019-03-10'),
(3, 'Football Club', 'Sports', '2017-10-15'),
(4, 'Music Society', 'Cultural', '2020-02-20');


INSERT INTO Membership (MembershipID, StudentID, ClubID, JoinDate, Role, Status) VALUES
(1, 1, 1, '2022-09-15', 'Member', 'Active'),
(2, 2, 2, '2021-11-02', 'President', 'Active'),
(3, 3, 1, '2023-01-20', 'Member', 'Active'),
(4, 4, 3, '2020-03-18', 'Captain', 'Active'),
(5, 5, 4, '2022-10-10', 'Treasurer', 'Active');


INSERT INTO Event (EventID, ClubID, EventName, EventDate, Venue, AttendanceLimit, Description) VALUES
(1, 1, 'AI Workshop', '2024-05-10', 'Innovation Lab', 50, 'Introduction to Machine Learning'),
(2, 2, 'Recycling Drive', '2024-04-22', 'Central Park', 100, 'Earth Day Cleanup'),
(3, 3, 'Inter-University Football Match', '2024-06-01', 'Main Stadium', 22, 'Friendly match with City College'),
(4, 4, 'Spring Concert', '2024-05-20', 'Auditorium Hall', 200, 'Live music performances by students');


INSERT INTO Participation (ParticipationID, StudentID, EventID, AttendanceStatus, Feedback)
VALUES
(1, 1, 1, 'Attended', 'Very informative session!'),
(2, 2, 2, 'Attended', 'Great teamwork and organization.'),
(3, 3, 1, 'Absent', NULL),
(4, 4, 3, 'Attended', 'Excellent match and great atmosphere.'),
(5, 5, 4, 'Attended', 'Loved the performances!');



INSERT INTO Announcement (AnnouncementID, ClubID, Title, Content, DateSent, SenderName) VALUES
(1, 1, 'New Workshop Announcement', 'Join us for the upcoming AI Basics Workshop on May 10.', '2024-04-25', 'Club President'),
(2, 2, 'Earth Day Activity', 'Green Planet invites volunteers for the Recycling Drive!', '2024-04-10', 'Event Coordinator'),
(3, 4, 'Spring Concert Line-Up', 'Our concert will feature student bands and solo acts.', '2024-04-30', 'Music Society Chair');



SELECT 
    s.StudentID, 
    s.FirstName, 
    s.LastName, 
    c.ClubName, 
    m.Role, 
    m.Status
FROM Membership m
JOIN Student s ON m.StudentID = s.StudentID
JOIN Club c ON m.ClubID = c.ClubID;



SELECT 
    c.ClubName,
    e.EventName,
    e.EventDate,
    e.Venue
FROM Event e
JOIN Club c ON e.ClubID = c.ClubID
ORDER BY c.ClubName;


INSERT INTO Event (EventID, ClubID, EventName, EventDate, Venue, AttendanceLimit)
VALUES (99, 999, 'Fake Event', '2024-12-01', 'Nowhere', 10);


SELECT 
    c.ClubName,
    COUNT(m.StudentID) AS MemberCount
FROM Club c
LEFT JOIN Membership m ON c.ClubID = m.ClubID
GROUP BY c.ClubName;


SELECT 
    e.EventName,
    COUNT(p.StudentID) AS TotalParticipants
FROM Event e
LEFT JOIN Participation p ON e.EventID = p.EventID
GROUP BY e.EventName;


SELECT 
    a.Title,
    a.Content,
    a.DateSent,
    c.ClubName
FROM Announcement a
JOIN Club c ON a.ClubID = c.ClubID
ORDER BY a.DateSent DESC;






DELIMITER $$





CREATE PROCEDURE sp_create_student(
    IN p_FirstName VARCHAR(50),
    IN p_LastName  VARCHAR(50),
    IN p_Email     VARCHAR(100),
    IN p_EnrollmentYear INT,
    IN p_Course    VARCHAR(100),
    OUT p_NewID    INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Student (FirstName, LastName, Email, EnrollmentYear, Course)
        VALUES (p_FirstName, p_LastName, p_Email, p_EnrollmentYear, p_Course);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_student(IN p_StudentID INT)
BEGIN
    SELECT * FROM Student WHERE StudentID = p_StudentID;
END$$

CREATE PROCEDURE sp_update_student(
    IN p_StudentID INT,
    IN p_FirstName VARCHAR(50),
    IN p_LastName  VARCHAR(50),
    IN p_Email     VARCHAR(100),
    IN p_EnrollmentYear INT,
    IN p_Course    VARCHAR(100),
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Student
        SET FirstName = p_FirstName,
            LastName = p_LastName,
            Email = p_Email,
            EnrollmentYear = p_EnrollmentYear,
            Course = p_Course
        WHERE StudentID = p_StudentID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_student(IN p_StudentID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        -- Optional check: prevent delete if memberships or participations exist
        IF ( (SELECT COUNT(*) FROM Membership WHERE StudentID = p_StudentID) > 0
             OR (SELECT COUNT(*) FROM Participation WHERE StudentID = p_StudentID) > 0 ) THEN
            SET p_ok = FALSE;
            ROLLBACK;
            LEAVE sp_delete_student;
        END IF;

        DELETE FROM Student WHERE StudentID = p_StudentID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$







CREATE PROCEDURE sp_create_club(
    IN p_ClubName VARCHAR(100),
    IN p_Category VARCHAR(50),
    IN p_FoundingDate DATE,
    OUT p_NewID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Club (ClubName, Category, FoundingDate)
        VALUES (p_ClubName, p_Category, p_FoundingDate);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_club(IN p_ClubID INT)
BEGIN
    SELECT * FROM Club WHERE ClubID = p_ClubID;
END$$

CREATE PROCEDURE sp_update_club(
    IN p_ClubID INT,
    IN p_ClubName VARCHAR(100),
    IN p_Category VARCHAR(50),
    IN p_FoundingDate DATE,
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Club
        SET ClubName = p_ClubName,
            Category = p_Category,
            FoundingDate = p_FoundingDate
        WHERE ClubID = p_ClubID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_club(IN p_ClubID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        -- Prevent deletion if related child records exist (Membership/Event/Announcement)
        IF ( (SELECT COUNT(*) FROM Membership WHERE ClubID = p_ClubID) > 0
             OR (SELECT COUNT(*) FROM Event WHERE ClubID = p_ClubID) > 0
             OR (SELECT COUNT(*) FROM Announcement WHERE ClubID = p_ClubID) > 0 ) THEN
            SET p_ok = FALSE;
            ROLLBACK;
            LEAVE sp_delete_club;
        END IF;

        DELETE FROM Club WHERE ClubID = p_ClubID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$







CREATE PROCEDURE sp_create_membership(
    IN p_StudentID INT,
    IN p_ClubID INT,
    IN p_JoinDate DATE,
    IN p_Role VARCHAR(50),
    IN p_Status VARCHAR(50),
    OUT p_NewID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Membership (StudentID, ClubID, JoinDate, Role, Status)
        VALUES (p_StudentID, p_ClubID, p_JoinDate, p_Role, p_Status);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_memberships_by_student(IN p_StudentID INT)
BEGIN
    SELECT m.*, c.ClubName
    FROM Membership m
    JOIN Club c ON m.ClubID = c.ClubID
    WHERE m.StudentID = p_StudentID;
END$$

CREATE PROCEDURE sp_update_membership(
    IN p_MembershipID INT,
    IN p_JoinDate DATE,
    IN p_Role VARCHAR(50),
    IN p_Status VARCHAR(50),
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Membership
        SET JoinDate = p_JoinDate,
            Role = p_Role,
            Status = p_Status
        WHERE MembershipID = p_MembershipID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_membership(IN p_MembershipID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        DELETE FROM Membership WHERE MembershipID = p_MembershipID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$








CREATE PROCEDURE sp_create_event(
    IN p_ClubID INT,
    IN p_EventName VARCHAR(100),
    IN p_EventDate DATE,
    IN p_Venue VARCHAR(100),
    IN p_AttendanceLimit INT,
    IN p_Description TEXT,
    OUT p_NewID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Event (ClubID, EventName, EventDate, Venue, AttendanceLimit, Description)
        VALUES (p_ClubID, p_EventName, p_EventDate, p_Venue, p_AttendanceLimit, p_Description);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_events_by_club(IN p_ClubID INT)
BEGIN
    SELECT * FROM Event WHERE ClubID = p_ClubID ORDER BY EventDate;
END$$

CREATE PROCEDURE sp_update_event(
    IN p_EventID INT,
    IN p_EventName VARCHAR(100),
    IN p_EventDate DATE,
    IN p_Venue VARCHAR(100),
    IN p_AttendanceLimit INT,
    IN p_Description TEXT,
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Event
        SET EventName = p_EventName,
            EventDate = p_EventDate,
            Venue = p_Venue,
            AttendanceLimit = p_AttendanceLimit,
            Description = p_Description
        WHERE EventID = p_EventID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_event(IN p_EventID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        -- Prevent deletion if participations exist (optionally)
        IF (SELECT COUNT(*) FROM Participation WHERE EventID = p_EventID) > 0 THEN
            SET p_ok = FALSE;
            ROLLBACK;
            LEAVE sp_delete_event;
        END IF;

        DELETE FROM Event WHERE EventID = p_EventID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$



CREATE PROCEDURE sp_create_participation(
    IN p_StudentID INT,
    IN p_EventID INT,
    IN p_AttendanceStatus VARCHAR(50),
    IN p_Feedback TEXT,
    OUT p_NewID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Participation (StudentID, EventID, AttendanceStatus, Feedback)
        VALUES (p_StudentID, p_EventID, p_AttendanceStatus, p_Feedback);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_participation_by_event(IN p_EventID INT)
BEGIN
    SELECT p.*, s.FirstName, s.LastName
    FROM Participation p
    JOIN Student s ON p.StudentID = s.StudentID
    WHERE p.EventID = p_EventID;
END$$

CREATE PROCEDURE sp_update_participation(
    IN p_ParticipationID INT,
    IN p_AttendanceStatus VARCHAR(50),
    IN p_Feedback TEXT,
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Participation
        SET AttendanceStatus = p_AttendanceStatus,
            Feedback = p_Feedback
        WHERE ParticipationID = p_ParticipationID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_participation(IN p_ParticipationID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        DELETE FROM Participation WHERE ParticipationID = p_ParticipationID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$




CREATE PROCEDURE sp_create_announcement(
    IN p_ClubID INT,
    IN p_Title VARCHAR(100),
    IN p_Content TEXT,
    IN p_DateSent DATE,
    IN p_SenderName VARCHAR(100),
    OUT p_NewID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_NewID = NULL;
    END;

    START TRANSACTION;
        INSERT INTO Announcement (ClubID, Title, Content, DateSent, SenderName)
        VALUES (p_ClubID, p_Title, p_Content, p_DateSent, p_SenderName);
        SET p_NewID = LAST_INSERT_ID();
    COMMIT;
END$$

CREATE PROCEDURE sp_get_announcements_by_club(IN p_ClubID INT)
BEGIN
    SELECT * FROM Announcement WHERE ClubID = p_ClubID ORDER BY DateSent DESC;
END$$

CREATE PROCEDURE sp_update_announcement(
    IN p_AnnouncementID INT,
    IN p_Title VARCHAR(100),
    IN p_Content TEXT,
    IN p_DateSent DATE,
    IN p_SenderName VARCHAR(100),
    OUT p_ok BOOL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        UPDATE Announcement
        SET Title = p_Title,
            Content = p_Content,
            DateSent = p_DateSent,
            SenderName = p_SenderName
        WHERE AnnouncementID = p_AnnouncementID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$

CREATE PROCEDURE sp_delete_announcement(IN p_AnnouncementID INT, OUT p_ok BOOL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ok = FALSE;
    END;

    START TRANSACTION;
        DELETE FROM Announcement WHERE AnnouncementID = p_AnnouncementID;
        SET p_ok = (ROW_COUNT() > 0);
    COMMIT;
END$$



DELIMITER ;


CREATE TABLE AuditLog (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    TableName VARCHAR(100),
    OperationType VARCHAR(10),
    RecordID INT,
    OldData TEXT,
    NewData TEXT,
    ChangedBy VARCHAR(100),
    ChangedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$

CREATE FUNCTION fn_student_data(p_StudentID INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE v_data TEXT;
    SELECT CONCAT('{"StudentID":', StudentID,
                  ', "Name":"', FirstName, ' ', LastName,
                  '", "Email":"', Email,
                  '", "Course":"', Course, '"}')
    INTO v_data
    FROM Student
    WHERE StudentID = p_StudentID;
    RETURN v_data;
END$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER trg_student_insert
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
    DECLARE v_user VARCHAR(100);
    SET v_user = CURRENT_USER();

    INSERT INTO AuditLog (TableName, OperationType, RecordID, NewData, ChangedBy)
    VALUES ('Student', 'INSERT', NEW.StudentID, fn_student_data(NEW.StudentID), v_user);
END$$

DELIMITER ;


DELIMITER $$

CREATE TRIGGER trg_student_update
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
    DECLARE v_user VARCHAR(100);
    SET v_user = CURRENT_USER();

    INSERT INTO AuditLog (TableName, OperationType, RecordID, OldData, NewData, ChangedBy)
    VALUES ('Student', 'UPDATE', NEW.StudentID, fn_student_data(OLD.StudentID), fn_student_data(NEW.StudentID), v_user);
END$$

DELIMITER ;


DELIMITER $$

CREATE TRIGGER trg_student_delete
AFTER DELETE ON Student
FOR EACH ROW
BEGIN
    DECLARE v_user VARCHAR(100);
    SET v_user = CURRENT_USER();

    INSERT INTO AuditLog (TableName, OperationType, RecordID, OldData, ChangedBy)
    VALUES ('Student', 'DELETE', OLD.StudentID, fn_student_data(OLD.StudentID), v_user);
END$$

DELIMITER ;



INSERT INTO Student (FirstName, LastName, Email, EnrollmentYear, Course)
VALUES ('Mark', 'Ross', 'mark.ross@example.com', 2024, 'Economics');


UPDATE Student
SET Course = 'Business Management'
WHERE StudentID = 1;


DELETE FROM Student
WHERE StudentID = 2;


SELECT * FROM AuditLog ORDER BY ChangedAt DESC;




DELIMITER $$

CREATE PROCEDURE sp_register_student_to_club(
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_Email VARCHAR(100),
    IN p_EnrollmentYear INT,
    IN p_Course VARCHAR(100),
    IN p_ClubID INT,
    IN p_Role VARCHAR(50),
    IN p_Status VARCHAR(20),
    IN p_EventID INT
)
BEGIN
    DECLARE v_StudentID INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       
        ROLLBACK;
        SELECT ' Transaction failed — all changes rolled back.' AS Result;
    END;

  
    START TRANSACTION;

   
    INSERT INTO Student (FirstName, LastName, Email, EnrollmentYear, Course)
    VALUES (p_FirstName, p_LastName, p_Email, p_EnrollmentYear, p_Course);

    SET v_StudentID = LAST_INSERT_ID();


    INSERT INTO Membership (StudentID, ClubID, JoinDate, Role, Status)
    VALUES (v_StudentID, p_ClubID, CURDATE(), p_Role, p_Status);

   
    INSERT INTO Participation (StudentID, EventID, AttendanceStatus, Feedback)
    VALUES (v_StudentID, p_EventID, 'Registered', NULL);


    COMMIT;
    SELECT 'Student successfully registered, membership created, and event participation logged.' AS Result;

END$$

DELIMITER ;



CALL sp_register_student_to_club(
    'Laura',
    'White',
    'laura.white@example.com',
    2025,
    'Computer Science',
    1,            -- ClubID (e.g., Tech Innovators)
    'Member', 
    'Active', 
    1             -- EventID (e.g., AI Workshop)
);


SELECT * FROM Student ORDER BY StudentID DESC LIMIT 1;
SELECT * FROM Membership ORDER BY MembershipID DESC LIMIT 1;
SELECT * FROM Participation ORDER BY ParticipationID DESC LIMIT 1;



CALL sp_register_student_to_club(
    'John',
    'Green',
    'john.green@example.com',
    2025,
    'Business Administration',
    999,         -- Invalid ClubID 
    'Member',
    'Active',
    1
);



CREATE INDEX idx_student_name ON Student (StudentName);


CREATE INDEX idx_club_category ON Club (Category);


CREATE INDEX idx_event_club_date ON Event (ClubID, EventDate);


CREATE INDEX idx_membership_student ON Membership (StudentID);


CREATE INDEX idx_participation_event ON Participation (EventID);



CREATE TABLE IF NOT EXISTS StudentAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    ActionType VARCHAR(50),
    ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UserPerformed VARCHAR(100)
);


DELIMITER //
CREATE TRIGGER trg_student_update
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
    INSERT INTO StudentAudit (StudentID, ActionType, UserPerformed)
    VALUES (OLD.StudentID, 'UPDATE', CURRENT_USER());
END //
DELIMITER ;

SELECT * FROM StudentAudit;


DELIMITER //
CREATE PROCEDURE AddNewMembership (
    IN p_StudentID INT,
    IN p_ClubID INT,
    IN p_JoinDate DATE,
    IN p_Role VARCHAR(50),
    IN p_Status VARCHAR(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction failed – all operations rolled back.' AS Message;
    END;

    START TRANSACTION;

    INSERT INTO Membership (StudentID, ClubID, JoinDate, Role, Status)
    VALUES (p_StudentID, p_ClubID, p_JoinDate, p_Role, p_Status);

    COMMIT;
    SELECT 'New membership successfully added.' AS Message;
END //
DELIMITER ;


CALL AddNewMembership(3, 2, '2024-05-01', 'Member', 'Active');








